﻿using System.Configuration;
using System.Data;
using System.Data.OracleClient;

namespace ParsePtb.util
{
    public class OracleHelper
    {
        private static string host = ConfigurationManager.AppSettings.Get("oracle.host");
        private static string port = ConfigurationManager.AppSettings.Get("oracle.port");
        private static string admin = ConfigurationManager.AppSettings.Get("oracle.admin");
        private static string password = ConfigurationManager.AppSettings.Get("oracle.password");
        private static string serviceName = ConfigurationManager.AppSettings.Get("oracle.servicename");

        // Unicode=True; 不加 导致新增 修改 中文字符集乱码
        public static string connectionString = $"Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = {host})(PORT = {port}))(CONNECT_DATA = (SERVICE_NAME = {serviceName}))); Persist Security Info = True; User ID = {admin}; Password = {password};Unicode=True;";

        //连接
        public static OracleConnection connection = null;



        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable [多行结果] 
        /// *** 常用于数据量比较大的情况下 ==》只填充到单表  如果是多表使用DataSet
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行查询语句的参数</param> 
        /// <returns></returns> 
        public static DataTable ExecuteDataTable(string sql, bool isProcess, params OracleParameter[] parameters)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand command = ConfigCommand(connection, sql, isProcess, parameters);
                OracleDataReader reader = command.ExecuteReader();
                DataTable data = new DataTable();
                if (reader != null)
                {
                    data.Load(reader, LoadOption.PreserveChanges, null);
                }
                return data;
            }
        }

        /// <summary>
        ///  配置OracleCommand
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="isProcess"></param>
        /// <returns></returns>
        private static OracleCommand ConfigCommand(OracleConnection connection, string sql, bool isProcess, OracleParameter[] parameters)
        {
            OracleCommand command = new OracleCommand();
            command.Connection = connection;
            command.CommandText = sql;
            if (isProcess)
            {
                command.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                command.CommandType = CommandType.Text;
            }

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            return command;
        }


        /// <summary>
        /// 通过insert into语句向数据库中插入数据
        /// </summary>
        /// <param name="insertSql">insert 语句</param>
        public static int ExecuteSqlNoQuery(string sql, bool isProcess, OracleParameter[] parameters = null)
        {
            //int affectedRows = 0;
            if (connection == null)
            {
                connection = new OracleConnection(connectionString);
            }

            CommandType commandType = isProcess ? CommandType.StoredProcedure : CommandType.Text;

            return ExecuteNonQuery(connection, commandType, sql, parameters);
        }

        /// <summary>
        /// 增删改操作使用此方法（需要一个存在的连接）
        /// </summary> 
        /// <param name="conn">一个存在的OracleConnection参数</param>
        /// <param name="commandType">命令类型（sql或者存储过程）</param>
        /// <param name="commandText">sql语句或者存储过程名称</param>
        /// <param name="commandParameters">命令所需参数数组</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            int affectRow = -1;
            OracleCommand cmd = null;
            /*try
            {*/
            // 创建一个OracleCommand
            cmd = new OracleCommand();
            //调用静态方法PrepareCommand完成赋值操作
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            //执行命令返回
            affectRow = cmd.ExecuteNonQuery();
            //清空参数
            //cmd.Parameters.Clear();
            /*}
            catch (Exception ex)
            {
                LogsHelper.AddErrorLog($"oracle操作异常:" + ex.Message);
            }
            finally
            {*/
            //清空参数
            cmd.Parameters.Clear();
            /*}*/
            return affectRow;
        }



        /// <summary>
        /// 一个静态的预处理函数
        /// </summary>
        /// <param name="cmd">存在的OracleCommand对象</param>
        /// <param name="conn">存在的OracleConnection对象</param>
        /// <param name="trans">存在的OracleTransaction对象</param>
        /// <param name="cmdType">命令类型（sql或者存在过程）</param>
        /// <param name="cmdText">sql语句或者存储过程名称</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {
            //如果连接未打开，先打开连接
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            //未要执行的命令设置参数
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //如果传入了事务，需要将命令绑定到指定的事务上去
            if (trans != null)
                cmd.Transaction = trans;

            //将传入的参数信息赋值给命令参数
            if (commandParameters != null)
            {
                cmd.Parameters.AddRange(commandParameters);
            }

        }

    }
}
